﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using MvcMovieCollection.Properties;

namespace MvcMovieCollection
{
    /// <summary>This is the main Web Service for the MvcMovieCollection project.
    /// It is used to perform all direct db calls.  With this service all database 
    /// acces and SQL calls are abstracted away from the main MVC paradigm. 
    /// All data from database is returned in serialized xml and must be inflated 
    /// appropiratly into model objects. In this project, this action is performed in the controllers.
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class SvcMovies : System.Web.Services.WebService
    {

        #region Properties/Members

        // Database connection object used throughout the service.
        // Connects to 'movies.accdb' located in App_Data.
        private System.Data.OleDb.OleDbConnection dbConnection;
        private System.Data.OleDb.OleDbConnection DbConnection
        {
            get
            {
                if (dbConnection == null)
                {
                    dbConnection = new System.Data.OleDb.OleDbConnection();
                    dbConnection.ConnectionString = Settings.Default.ConnectionString;
                }

                return dbConnection;
            }
        }

        #endregion
        
        /// <summary>Calls DB to get all the movies and their associated
        /// genres.
        /// </summary>
        /// <returns></returns>
        [WebMethod]
        public string GetMovies()
        {

            System.Data.OleDb.OleDbCommand command =
                new System.Data.OleDb.OleDbCommand();

            command.Connection = this.DbConnection;

            command.CommandText = @"
                select Movies.id, title, Movies.description, rel_date, genre 
                from Movies left join Genres on Movies.genre_id = Genres.id                 
                ";

            System.Data.OleDb.OleDbDataReader reader;
            Models.Movie movie = new Models.Movie();
            System.Xml.Linq.XElement movieList =
                new System.Xml.Linq.XElement("MovieList");
            System.Xml.Linq.XElement movieXml =
                new System.Xml.Linq.XElement("Movie");
            try
            {
                this.DbConnection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    int id = int.Parse(reader["id"].ToString());
                    string relDate;
                    
                    movie.ID = id;
                    movie.Title = reader["title"].ToString();
                    movie.Description = reader["description"].ToString();

                    TrimDate(reader["rel_date"].ToString(), out relDate);
                    movie.ReleaseDate = relDate;

                    movie.Genre = reader["genre"].ToString();

                    movieXml = new System.Xml.Linq.XElement("Movie");
                    movieXml.SetElementValue("id", movie.ID);
                    movieXml.SetElementValue("Title", movie.Title);
                    movieXml.SetElementValue("Description", movie.Description);
                    movieXml.SetElementValue("ReleaseDate", movie.ReleaseDate);
                    movieXml.SetElementValue("Genre", movie.Genre);

                    movieList.Add(movieXml);                    
                }

            }
            catch (Exception)
            {
                return "EGetMovies";
            }
            finally
            {
                this.DbConnection.Close();
            }
            
            return movieList.ToString();
        }

        /// <summary>Gets a single record for a movie matching
        /// the given id.
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [WebMethod]
        public string GetMovie(int id)
        {
            System.Data.OleDb.OleDbCommand command =
                new System.Data.OleDb.OleDbCommand();

            command.Connection = this.DbConnection;

            command.CommandText = @"
                select id, title, description, rel_date, genre_id
                from Movies where id=@id 
                ";

            command.Parameters.AddWithValue("@id", id);

            System.Data.OleDb.OleDbDataReader reader;
            Models.EditMovie movie = new Models.EditMovie();
            System.Xml.Linq.XElement movieXml =
                new System.Xml.Linq.XElement("Movie");
            try
            {
                this.DbConnection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {

                    int genreId = int.Parse(reader["genre_id"].ToString());
                    
                    movie.ID = id;
                    movie.Title = reader["title"].ToString();
                    movie.Description = reader["description"].ToString();
                    movie.ReleaseDate = reader["rel_date"].ToString();

                    string relDate;
                    TrimDate(reader["rel_date"].ToString(), out relDate);
                    movie.ReleaseDate = relDate;

                    movie.GenreID = genreId;

                    movieXml.SetElementValue("id", movie.ID);
                    movieXml.SetElementValue("Title", movie.Title);
                    movieXml.SetElementValue("Description", movie.Description);
                    movieXml.SetElementValue("ReleaseDate", movie.ReleaseDate);
                    movieXml.SetElementValue("GenreID", movie.GenreID);
                }

            }
            catch (Exception)
            {
                return "EGetMovie";
            }
            finally
            {
                this.DbConnection.Close();
            }

            return movieXml.ToString();
        }

        /// <summary>Edits a movie with provided info.
        /// </summary>
        /// <param name="movieID">Id of movie to be edited.</param>
        /// <param name="title">New title of movie.</param>
        /// <param name="desc">New Description.</param>
        /// <param name="release">New release date.</param>
        /// <param name="genreID">Id of associated genre.</param>
        /// <returns></returns>
        [WebMethod]
        public int EditMovie(int movieID, string title, string desc, string release, int genreID)
        {
            System.Data.OleDb.OleDbCommand command =
                new System.Data.OleDb.OleDbCommand();

            command.Connection = this.DbConnection;

            if (release != null)
            {
                command.CommandText = @"
                update Movies
                set title = @title,
                description = @desc,
                rel_date = @release,
                genre_id = @genreID where id = @id
                ";

                command.Parameters.AddWithValue("@title", title);
                command.Parameters.AddWithValue("@desc", desc);
                command.Parameters.AddWithValue("@release", release);
                command.Parameters.AddWithValue("@genreID", genreID);
                command.Parameters.AddWithValue("@id", movieID);
            }
            else
            {
                command.CommandText = @"
                update Movies
                set title = @title,
                description = @desc,
                genre_id = @genreID where id = @id
                ";

                command.Parameters.AddWithValue("@title", title);
                command.Parameters.AddWithValue("@desc", desc);
                command.Parameters.AddWithValue("@genreID", genreID);
                command.Parameters.AddWithValue("@id", movieID);
            }

            int affectedRows = 0;
            try
            {
                this.DbConnection.Open();
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                this.DbConnection.Close();
            }

            return affectedRows;
        }

        /// <summary>Deletes a movie matching the given id.
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [WebMethod]
        public int DeleteMovie(int id)
        {
            System.Data.OleDb.OleDbCommand command =
                new System.Data.OleDb.OleDbCommand();

            command.Connection = this.DbConnection;

            command.CommandText = @"
                delete from Movies where id = @id
                ";

            command.Parameters.AddWithValue("@id", id);

            int affectedRows = 0;
            try
            {
                this.DbConnection.Open();
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                this.DbConnection.Close();
            }

            return affectedRows;
        }

        /// <summary>Deletes a genre with the given id.
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [WebMethod]
        public int DeleteGenre(int id)
        {
            System.Data.OleDb.OleDbCommand command =
                new System.Data.OleDb.OleDbCommand();

            command.Connection = this.DbConnection;

            command.CommandText = @"
                delete from Genres where id = @id
                ";

            command.Parameters.AddWithValue("@id", id);

            int affectedRows = 0;
            try
            {
                this.DbConnection.Open();
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                this.DbConnection.Close();
            }

            return affectedRows;
        }

        /// <summary>Gets all genres in the genres table.
        /// </summary>
        /// <returns></returns>
        [WebMethod]
        public string GetGenres()
        {
            System.Data.OleDb.OleDbCommand command =
                new System.Data.OleDb.OleDbCommand();

            command.Connection = this.DbConnection;

            command.CommandText = @"
                select id, genre from Genres
                ";

            System.Data.OleDb.OleDbDataReader reader;
            Models.Genres genre = new Models.Genres();
            System.Xml.Linq.XElement modelList =
                new System.Xml.Linq.XElement("GenreList");
            System.Xml.Linq.XElement genreXml; 

            try
            {
                this.DbConnection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    int id = int.Parse(reader["id"].ToString());
                    genre.ID = id;
                    genre.Name = reader["genre"].ToString();

                    genreXml = new System.Xml.Linq.XElement("Genre");
                    genreXml.SetElementValue("ID", genre.ID);
                    genreXml.SetElementValue("Name", genre.Name);

                    modelList.Add(genreXml);
                }
            }
            catch (Exception)
            {
                return "EGetGenres";
            }
            finally
            {
                this.DbConnection.Close();
            }

            return modelList.ToString();
        }

        /// <summary>Searches the db for a movie with the provided data.
        /// </summary>
        /// <param name="title">Title of movie.</param>
        /// <param name="description">Description of movie.</param>
        /// <param name="genreID">Genre id of associated genre.</param>
        /// <returns></returns>
        [WebMethod]
        public string Search(string title, string description, int genreID)
        {
            System.Data.OleDb.OleDbCommand command =
                new System.Data.OleDb.OleDbCommand();

            command.Connection = this.DbConnection;

            if (genreID == 0)
            {
                command.CommandText = @"
                    select Movies.id, title, Movies.description, rel_date, genre 
                    from Movies inner join Genres on Movies.genre_id = Genres.id
                    where title like @title and Movies.description like @description          
                ";
            }
            else
            {
                command.CommandText = @"
                    select Movies.id, title, Movies.description, rel_date, genre 
                    from Movies inner join Genres on Movies.genre_id = Genres.id
                    where title like @title and Movies.description like @description
                    and Genres.id = @genreID          
                ";
            }

            command.Parameters.AddWithValue("@title", "%"+title+"%");
            command.Parameters.AddWithValue("@description", "%" + description + "%");
            command.Parameters.AddWithValue("@genreID", genreID);        

            System.Data.OleDb.OleDbDataReader reader;
            Models.Movie movie = new Models.Movie();
            System.Xml.Linq.XElement movieList =
                new System.Xml.Linq.XElement("MovieList");
            System.Xml.Linq.XElement movieXml =
                new System.Xml.Linq.XElement("Movie");

            try
            {
                this.DbConnection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    movie.ID = int.Parse(reader["id"].ToString());
                    movie.Title = reader["title"].ToString();
                    movie.Description = reader["description"].ToString();

                    string relDate;
                    TrimDate(reader["rel_date"].ToString(), out relDate);
                    movie.ReleaseDate = relDate;

                    movie.Genre = reader["genre"].ToString();

                    movieXml = new System.Xml.Linq.XElement("Movie");
                    movieXml.SetElementValue("id", movie.ID);
                    movieXml.SetElementValue("Title", movie.Title);
                    movieXml.SetElementValue("Description", movie.Description);
                    movieXml.SetElementValue("ReleaseDate", movie.ReleaseDate);
                    movieXml.SetElementValue("Genre", movie.Genre);

                    movieList.Add(movieXml); 
                }
            }
            catch (Exception)
            {
                return "ESearch";
            }
            finally
            {
                this.DbConnection.Close();
            }

            return movieList.ToString();
        }

        /// <summary>Adds a new movie to the db with given data.
        /// </summary>
        /// <param name="title"></param>
        /// <param name="description"></param>
        /// <param name="relDate"></param>
        /// <param name="genreID"></param>
        /// <returns></returns>
        [WebMethod]
        public int AddNewMovie(string title, string description, string relDate, int genreID)
        {
            System.Data.OleDb.OleDbCommand command =
                new System.Data.OleDb.OleDbCommand();

            command.Connection = this.DbConnection;

            if (description == null)
            {
                description = string.Empty;
            }


            if (relDate != null)
            {
                command.CommandText = @"
                insert into Movies (title, description, rel_date, genre_id)
                values(@title, @description, @relDate, @genreID)
                ";

                command.Parameters.AddWithValue("@title", title);
                command.Parameters.AddWithValue("@description", description);
                command.Parameters.AddWithValue("@relDate", relDate);
                command.Parameters.AddWithValue("@genreID", genreID);
            }
            else 
            {
                command.CommandText = @"
                insert into Movies (title, description, genre_id)
                values(@title, @description, @genreID)
                ";

                command.Parameters.AddWithValue("@title", title);
                command.Parameters.AddWithValue("@description", description);
                command.Parameters.AddWithValue("@genreID", genreID);
            }            

            int affectedRows = 0;
            try
            {
                this.DbConnection.Open();
                affectedRows = command.ExecuteNonQuery();                
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                this.DbConnection.Close();
            }

            return affectedRows;
        }        

        /// <summary>Adds a new genre to the genres table.
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        [WebMethod]
        public int AddNewGenre(string name)
        {
            System.Data.OleDb.OleDbCommand command =
                new System.Data.OleDb.OleDbCommand();

            command.Connection = this.DbConnection;

            command.CommandText = @"
                insert into Genres (genre)
                values(@name)
                ";

            command.Parameters.AddWithValue("@name", name);

            int affectedRows = 0;

            try
            {
                this.DbConnection.Open();
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                this.DbConnection.Close();
            }

            return affectedRows;
        }

        #region Helper methods

        // Helper method to trim DateTime object.  
        // Date comes as date and default time. 
        // This method trims the time off and outputs
        // result into output variable 'ret.'
        private void TrimDate(string date, out string ret)
        {
            string[] trimmed = date.Split(new char[] { ' ' });
            ret = trimmed[0];
        }

        #endregion
    }
}
